﻿Imports DTO
Imports System.Data.OleDb

Public Class PhieuThuTien_DAO

#Region "Load Bang CMND len Combobox"
   
    Public Shared Function LayCMND() As DataTable

        Dim BangKhachHang As New DataTable
        Dim conn As New OleDbConnection()
        conn = DataProvider.Open()

        Dim sql As String
        sql = "Select * from KhachHang"
        Dim dt As New OleDbDataAdapter(sql, conn)
        dt.Fill(BangKhachHang)
        Return BangKhachHang

    End Function

#End Region

#Region "Lấy thông tin khách hàng dựa vào CMND"
    Public Shared Function LayKhachHang(ByVal cmnd As String) As DataTable
        Dim BangKhachHang As New DataTable
        Dim conn As New OleDbConnection()
        conn = DataProvider.Open()
        Dim sql As String = String.Format("Select * from KhachHang where CMND = '{0}'", cmnd)
        Dim dt As New OleDbDataAdapter(sql, conn)
        dt.Fill(BangKhachHang)
        Return BangKhachHang
    End Function
#End Region

#Region "Nhập dữ liệu vào bảng phiếu thu"
    Public Shared Function NhapPhieuThu(ByVal PhieuThu As PhieuThu_DTO) As Integer
        Dim sql As String = String.Format("INSERT INTO PhieuThu(NgayThu,MaKH,TienThu) VALUES ('{0}',{1},'{2}')", PhieuThu.NgayThu, PhieuThu.MaKH, PhieuThu.TienThu)
        Dim flag As Integer = DataProvider.ExcuteNonQuery(sql)
        sql = String.Format("update KhachHang set TienNo = TienNo - {0} where MaKH = {1}", PhieuThu.TienThu, PhieuThu.MaKH)
        flag = DataProvider.ExcuteNonQuery(sql)
        Return flag
    End Function
#End Region

#Region "Load danh sách phiếu thu"
    Public Shared Function LoadPhieuThu() As DataTable
        Dim sql As String = String.Format(" SELECT kh.TenKH,kh.CMND,pt.NgayThu,pt.TienThu from KhachHang kh,PhieuThu pt where pt.MaKH=kh.MaKH")
        Dim tb As DataTable = DataProvider.ExcuteReader(sql)
        Return tb
    End Function
#End Region
End Class
